package top.timous.mapper;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import top.timous.pojo.EpidemicInfo;
import top.timous.vo.EpidemicTotalInfo;

import java.util.List;

@Mapper
public interface EpidemicMapper {

    @Insert("INSERT INTO `epidemics`(`province_id`,`data_year`,`data_month`,`data_day`,`affirmed`,`suspected`,`isolated`,`dead`,`cured`,`user_id`,`input_date`)\n" +
            "VALUES(#{provinceId},#{dataYear},#{dataMonth},#{dataDay},#{affirmed},#{suspected},#{isolated},#{dead},#{cured},#{userId},#{InputDate})")
    void input(EpidemicInfo epidemicInfo);


    @Select("SELECT e.`province_id` , temp.`province_name` , e.`data_year` , e.`data_month` , e.`data_month` ,\n" +
            "temp.affirmed_total , temp.suspected_total , temp.isolated_total ,temp.dead_total , temp.cured_total\n" +
            "FROM `epidemics` AS e\n" +
            "RIGHT JOIN (\n" +
            "\tSELECT epid.`province_id` , pro.`province_name`, \n" +
            "\tSUM(`affirmed`) AS affirmed_total ,\n" +
            "\tSUM(`suspected`) AS suspected_total ,\n" +
            "\tSUM(`isolated`) AS isolated_total,\n" +
            "\tSUM(`dead`) AS dead_total,\n" +
            "\tSUM(`cured`) AS cured_total\n" +
            "\tFROM `epidemics` AS epid\n" +
            "\tRIGHT JOIN `provinces` AS pro\n" +
            "\tON epid.`province_id` = pro.`province_id`\n" +
            "\tGROUP BY epid.`province_id` , pro.`province_name`\n" +
            ") AS temp\n" +
            "ON e.`province_id` = temp.`province_id`\n" +
            "WHERE e.`data_year` = #{year} AND e.`data_month` = #{month} AND e.`data_day` = #{day}")
    List<EpidemicTotalInfo> EpidemicDetail(@Param("year") int year , @Param("month") int month , @Param("day") int day);


    @Select("SELECT epid.`province_id` ,\n" +
            "\t\t       pro.`province_name`, \n" +
            "\t\t       SUM(`affirmed`) AS affirmed_total , \n" +
            "\t\t       SUM(`suspected`) AS suspected_total , \n" +
            "\t\t       SUM(`isolated`) AS isolated_total, \n" +
            "\t\t       SUM(`dead`) AS dead_total, \n" +
            "\t\t       SUM(`cured`) AS cured_total \n" +
            "\t\t       FROM `epidemics` AS epid \n" +
            "\t\t       LEFT JOIN `provinces` AS pro \n" +
            "\t\t       ON epid.`province_id` = pro.`province_id` \n" +
            "\t\t       GROUP BY epid.`province_id` , pro.`province_name`")
    List<EpidemicTotalInfo> EpidemicDetails();

}
